﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Db4objects.Db4o;
using NmbpProject2.DboClasses;
using Oracle.DataAccess.Client;

namespace NmbpProject2.DataLayer {

  internal class PersonDL {

    /// <summary>
    /// Dohvaća sve osobe iz baze podataka.
    /// </summary>
    /// <returns></returns>
    public List<Person> getAllPeople () {
      var people = new List<Person> ();
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IObjectSet result = DataConfig.db.QueryByExample (typeof (Person));
          foreach (Person person in result) {
            people.Add (person);
          }
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          var oracle = new OracleConnectionClass ();
          oracle.openConnection ();
          var cmd = new OracleCommand ("SELECT * FROM people", oracle.conn);
          cmd.CommandType = CommandType.Text;
          var dt = new DataTable ();
          new OracleDataAdapter (cmd).Fill (dt);
          foreach (DataRow row in dt.Rows) {
            string firstName = row[0].ToString ();
            string lastName = row[1].ToString ();
            long jmbg = Convert.ToInt64 (row[2]);
            people.Add (new Person (firstName, lastName, jmbg));
          }
          oracle.closeConnection ();
          break;
        case dbtype.Xml:
          break;
      }
      return people;
    }

    /// <summary>
    /// Upisuje novi zapis osobe u bazu podataka.
    /// </summary>
    /// <param name="firstName"></param>
    /// <param name="lastName"></param>
    /// <param name="jmbg"></param>
    public void insertPerson (string firstName, string lastName, long jmbg) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          Person person = new Person (firstName, lastName, jmbg);
          DataConfig.db.Store (person);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.Connection = oracle.conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = String.Format (
              "INSERT INTO people VALUES ('{0}', '{1}', '{2}')",
              firstName, lastName, jmbg
              );
            cmd.ExecuteNonQuery ();
            oracle.closeConnection ();
          }
          catch (Exception e) {
            MessageBox.Show (e.Message, "ERROR");
          }
          break;
        case dbtype.Xml:
          break;
      }
    }

    /// <summary>
    /// Ažurira zapis osobe u bazi podataka. 
    /// </summary>
    /// <param name="person"></param>
    /// <param name="firstName"></param>
    /// <param name="lastName"></param>
    /// <param name="jmbg"></param>
    public void update (Person person, string firstName, string lastName, long jmbg) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          Person updatedPerson = (Person) DataConfig.db.QueryByExample (person)[0];
          updatedPerson.FirstName = firstName;
          updatedPerson.LastName = lastName;
          updatedPerson.Jmbg = jmbg;
          DataConfig.db.Store (updatedPerson);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.Connection = oracle.conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = String.Format (
              "UPDATE people SET firstName = '{0}', lastName = '{1}', jmbg = {2} WHERE jmbg={3}",
              firstName, lastName, jmbg, person.Jmbg);
            cmd.ExecuteNonQuery ();
            oracle.closeConnection ();
          }
          catch (Exception e) {
            MessageBox.Show (e.Message, "ERROR");
          }
          break;
        case dbtype.Xml:
          break;
      }
    }

    /// <summary>
    /// Dohvaća osobu iz baze podataka po jmbg-u.
    /// </summary>
    /// <param name="jmbg"></param>
    /// <returns></returns>
    public Person findByJmbg (long jmbg) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IList<Person> people = DataConfig.db.Query (delegate (Person match) { return match.Jmbg == jmbg; });
          if (people.Count > 0) {
            Person person = people[0];
            return person;
          }
          return null;
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          break;
        case dbtype.Xml:
          break;
      }
      return null;
    }

    /// <summary>
    /// Briše zapis osobe iz baze podataka.
    /// </summary>
    /// <param name="person"></param>
    public void delete (Person person) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          DataConfig.db.Delete (person);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = oracle.conn;
            cmd.CommandText = string.Format ("DELETE FROM applications WHERE DEREF(person).jmbg = '{0}'", person.Jmbg);
            cmd.ExecuteNonQuery ();
            cmd.CommandText = string.Format ("DELETE FROM people WHERE jmbg = '{0}'", person.Jmbg);
            cmd.ExecuteNonQuery ();
          }
          catch (Exception e) {
            MessageBox.Show (e.Message, "ERROR");
          }
          break;
        case dbtype.Xml:
          break;
      }
    }
  }
}